﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Backend.DTO;
using System.Data;

namespace Backend.DAL
{
    class PatientDAL
    {
        private SqlConnection sqlConnection;

        public PatientDAL()
        {
            DbConn aConn = new DbConn();
            sqlConnection = aConn.GetUssdSqlConnection;
        }


        public List<PatientDTO> GetPatientsByStudyId(int studyId)
        {
            List<PatientDTO> listOfPatients = new List<PatientDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM Patients, StudyPatient WHERE Patients.Id = StudyPatient.PatientId AND StudyId = @StudyId ORDER BY Patients.Id", sqlConnection);
                command.Parameters.AddWithValue("StudyId", studyId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        PatientDTO aPatientDTO = new PatientDTO();

                        aPatientDTO.Id = Convert.ToInt32(reader["Id"]);

                        /*--- A Section Parameters (37 fields) ---*/
                        aPatientDTO.Param_NationalRegistrationNumber = reader["Param_NationalRegistrationNumber"].ToString();
                        aPatientDTO.Param_Sex = reader["Param_Sex"].ToString();
                        aPatientDTO.Param_Age = Convert.ToInt32(reader["Param_Age"]);
                        aPatientDTO.Param_DateOfAdmission = Convert.ToDateTime(reader["Param_DateOfAdmission"]);
                        aPatientDTO.Param_TimeOfAdmission = Convert.ToDateTime(reader["Param_TimeOfAdmission"]);
                        aPatientDTO.Param_EarlierEpisodesOfSbo = Convert.ToBoolean(reader["Param_EarlierEpisodesOfSbo"]);
                        aPatientDTO.Param_EarlierAbdominalSurgery = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery"]);
                        aPatientDTO.Param_EarlierAbdominalSurgery2 = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery2"]);
                        aPatientDTO.Param_EarlierAbdominalSurgery3 = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery3"]);
                        aPatientDTO.Param_Hypertension = Convert.ToBoolean(reader["Param_Hypertension"]);
                        aPatientDTO.Param_AnginaPectoris = Convert.ToBoolean(reader["Param_AnginaPectoris"]);
                        aPatientDTO.Param_HeartFailure = Convert.ToBoolean(reader["Param_HeartFailure"]);
                        aPatientDTO.Param_Ami = Convert.ToBoolean(reader["Param_Ami"]);
                        aPatientDTO.Param_StrokeTia = Convert.ToBoolean(reader["Param_StrokeTia"]);
                        aPatientDTO.Param_KidneyFailure = Convert.ToBoolean(reader["Param_KidneyFailure"]);
                        aPatientDTO.Param_Diabetes = Convert.ToBoolean(reader["Param_Diabetes"]);
                        aPatientDTO.Param_PulmonaryDisease = Convert.ToInt32(reader["Param_PulmonaryDisease"]);
                        aPatientDTO.Param_OtherChronicDisease = Convert.ToBoolean(reader["Param_OtherChronicDisease"]);
                        aPatientDTO.Param_TimeForRadiologyAnswer = Convert.ToDateTime(reader["Param_TimeForRadiologyAnswer"]);
                        aPatientDTO.Param_Surgery = Convert.ToBoolean(reader["Param_Surgery"]);
                        aPatientDTO.Param_DateForSurgicalAnnouncment = Convert.ToDateTime(reader["Param_DateForSurgicalAnnouncment"]);
                        aPatientDTO.Param_DateForSurgery = Convert.ToDateTime(reader["Param_DateForSurgery"]);
                        aPatientDTO.Param_TimeForSurgicalAnnouncement = Convert.ToDateTime(reader["Param_TimeForSurgicalAnnouncement"]);
                        aPatientDTO.Param_TimeUntilSurgery = Convert.ToDateTime(reader["Param_TimeUntilSurgery"]);
                        aPatientDTO.Param_TimeUntilSurgeryAccordingToOrbit = Convert.ToDateTime(reader["Param_TimeUntilSurgeryAccordingToOrbit"]);
                        aPatientDTO.Param_Diagnosis = reader["Param_Diagnosis"].ToString();

                        if (string.IsNullOrEmpty(reader["Param_DiagnosisInDischargeNote"].ToString()))
                        {
                            aPatientDTO.Param_DiagnosisInDischargeNote = null;
                        }
                        else
                        {
                            aPatientDTO.Param_DiagnosisInDischargeNote = Convert.ToInt32(reader["Param_DiagnosisInDischargeNote"]);
                        }

                        if (string.IsNullOrEmpty(reader["Param_MalignantDiagnosis"].ToString()))
                        {
                            aPatientDTO.Param_MalignantDiagnosis = null;
                        }
                        else
                        {
                            aPatientDTO.Param_MalignantDiagnosis = Convert.ToInt32(reader["Param_MalignantDiagnosis"]);
                        }

                        aPatientDTO.Param_TimeUntilBowelMovement = Convert.ToInt32(reader["Param_TimeUntilBowelMovement"]);
                        aPatientDTO.Param_ReSurgery = Convert.ToBoolean(reader["Param_ReSurgery"]);
                        aPatientDTO.Param_DaysAdmitted = Convert.ToDateTime(reader["Param_DaysAdmitted"]);
                        aPatientDTO.Param_HoursAdmitted = Convert.ToDateTime(reader["Param_HoursAdmitted"]);
                        aPatientDTO.Param_Dead = Convert.ToBoolean(reader["Param_Dead"]);
                        aPatientDTO.Param_DateOfDeath = Convert.ToDateTime(reader["Param_DateOfDeath"]);
                        aPatientDTO.Param_DeadWithin30DaysFromAdmission = Convert.ToBoolean(reader["Param_DeadWithin30DaysFromAdmission"]);
                        aPatientDTO.Param_ReAdmissionDueToSbo = Convert.ToBoolean(reader["Param_ReAdmissionDueToSbo"]);
                        aPatientDTO.Param_DateForReAdmission = Convert.ToDateTime(reader["Param_DateForReAdmission"]);

                        /*--- B Section Parameters (5 fields) ---*/
                        aPatientDTO.Param_CrpD0 = Convert.ToInt32(reader["Param_CrpD0"]);
                        aPatientDTO.Param_CrpMax = Convert.ToInt32(reader["Param_CrpMax"]);
                        aPatientDTO.Param_KreaD0 = Convert.ToInt32(reader["Param_KreaD0"]);
                        aPatientDTO.Param_KreaMax = Convert.ToInt32(reader["Param_KreaMax"]);
                        aPatientDTO.Param_AbdominalPain = Convert.ToBoolean(reader["Param_AbdominalPain"]);

                        /*--- C Section Parameters (11 fields) ---*/
                        aPatientDTO.Param_VomitBeforeAdmission = Convert.ToBoolean(reader["Param_VomitBeforeAdmission"]);
                        aPatientDTO.Param_VsondInitially = Convert.ToBoolean(reader["Param_VsondInitially"]);

                        if (string.IsNullOrEmpty(reader["Param_VsondLossesMax"].ToString()))
                        {
                            aPatientDTO.Param_VsondLossesMax = null;
                        }
                        else
                        {
                            aPatientDTO.Param_VsondLossesMax = Convert.ToInt32(reader["Param_VsondLossesMax"]);
                        }

                        aPatientDTO.Param_LengthOfHistory = Convert.ToInt32(reader["Param_LengthOfHistory"]);
                        aPatientDTO.Param_ClinicalEvaluation = Convert.ToInt32(reader["Param_ClinicalEvaluation"]);
                        aPatientDTO.Param_TypeOfSurgery = Convert.ToInt32(reader["Param_TypeOfSurgery"]);
                        aPatientDTO.Param_2ndSurgery = Convert.ToInt32(reader["Param_2ndSurgery"]);
                        aPatientDTO.Param_WasRadiologicAnswerCorrect = Convert.ToInt32(reader["Param_WasRadiologicAnswerCorrect"]);
                        aPatientDTO.Param_TimeUnderSurgery = Convert.ToInt32(reader["Param_TimeUnderSurgery"]);
                        aPatientDTO.Param_PostoperativeComplications = Convert.ToInt32(reader["Param_PostoperativeComplications"]);
                        aPatientDTO.Param_DaysAtIcu = Convert.ToInt32(reader["Param_DaysAtIcu"]);

                        /*--- D Section Parameters (7 fields) ---*/
                        aPatientDTO.Param_1stRadiology = Convert.ToInt32(reader["Param_1stRadiology"]);
                        aPatientDTO.Param_2ndRadiology = Convert.ToInt32(reader["Param_2ndRadiology"]);
                        aPatientDTO.Param_3rdRadiology = Convert.ToInt32(reader["Param_3rdRadiology"]);
                        aPatientDTO.Param_4thRadiology = Convert.ToInt32(reader["Param_4thRadiology"]);
                        aPatientDTO.Param_5thRadiology = Convert.ToInt32(reader["Param_5thRadiology"]);
                        aPatientDTO.Param_RadiologyAnswer = Convert.ToInt32(reader["Param_RadiologyAnswer"]);
                        aPatientDTO.Param_CirculatoryAffectedBowels = Convert.ToBoolean(reader["Param_CirculatoryAffectedBowels"]);

                        /*--- Extra field Parameters ---*/
                        aPatientDTO.Param_NumberOfReAdmissions = reader["Param_NumberOfReAdmissions"].ToString();
                        aPatientDTO.IsReviewed = Convert.ToBoolean(reader["IsReviewed"]);
                        aPatientDTO.IsFlagged = Convert.ToBoolean(reader["IsFlagged"]);

                        listOfPatients.Add(aPatientDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfPatients;
        }

        public PatientDTO GetPatient(int id)
        {
            PatientDTO aPatientDTO = null;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT * FROM Patients WHERE Id = @PatientId", sqlConnection);
                    command.Parameters.AddWithValue("PatientId", id);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            aPatientDTO = new PatientDTO();

                            aPatientDTO.Id = Convert.ToInt32(reader["Id"]);

                            /*--- A Section Parameters (37 fields) ---*/
                            aPatientDTO.Param_NationalRegistrationNumber = reader["Param_NationalRegistrationNumber"].ToString();
                            aPatientDTO.Param_Sex = reader["Param_Sex"].ToString();
                            aPatientDTO.Param_Age = Convert.ToInt32(reader["Param_Age"]);
                            aPatientDTO.Param_DateOfAdmission = Convert.ToDateTime(reader["Param_DateOfAdmission"]);
                            aPatientDTO.Param_TimeOfAdmission = Convert.ToDateTime(reader["Param_TimeOfAdmission"]);
                            aPatientDTO.Param_EarlierEpisodesOfSbo = Convert.ToBoolean(reader["Param_EarlierEpisodesOfSbo"]);
                            aPatientDTO.Param_EarlierAbdominalSurgery = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery"]);
                            aPatientDTO.Param_EarlierAbdominalSurgery2 = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery2"]);
                            aPatientDTO.Param_EarlierAbdominalSurgery3 = Convert.ToInt32(reader["Param_EarlierAbdominalSurgery3"]);
                            aPatientDTO.Param_Hypertension = Convert.ToBoolean(reader["Param_Hypertension"]);
                            aPatientDTO.Param_AnginaPectoris = Convert.ToBoolean(reader["Param_AnginaPectoris"]);
                            aPatientDTO.Param_HeartFailure = Convert.ToBoolean(reader["Param_HeartFailure"]);
                            aPatientDTO.Param_Ami = Convert.ToBoolean(reader["Param_Ami"]);
                            aPatientDTO.Param_StrokeTia = Convert.ToBoolean(reader["Param_StrokeTia"]);
                            aPatientDTO.Param_KidneyFailure = Convert.ToBoolean(reader["Param_KidneyFailure"]);
                            aPatientDTO.Param_Diabetes = Convert.ToBoolean(reader["Param_Diabetes"]);
                            aPatientDTO.Param_PulmonaryDisease = Convert.ToInt32(reader["Param_PulmonaryDisease"]);
                            aPatientDTO.Param_OtherChronicDisease = Convert.ToBoolean(reader["Param_OtherChronicDisease"]);
                            aPatientDTO.Param_TimeForRadiologyAnswer = Convert.ToDateTime(reader["Param_TimeForRadiologyAnswer"]);
                            aPatientDTO.Param_Surgery = Convert.ToBoolean(reader["Param_Surgery"]);
                            aPatientDTO.Param_DateForSurgicalAnnouncment = Convert.ToDateTime(reader["Param_DateForSurgicalAnnouncment"]);
                            aPatientDTO.Param_DateForSurgery = Convert.ToDateTime(reader["Param_DateForSurgery"]);
                            aPatientDTO.Param_TimeForSurgicalAnnouncement = Convert.ToDateTime(reader["Param_TimeForSurgicalAnnouncement"]);
                            aPatientDTO.Param_TimeUntilSurgery = Convert.ToDateTime(reader["Param_TimeUntilSurgery"]);
                            aPatientDTO.Param_TimeUntilSurgeryAccordingToOrbit = Convert.ToDateTime(reader["Param_TimeUntilSurgeryAccordingToOrbit"]);
                            aPatientDTO.Param_Diagnosis = reader["Param_Diagnosis"].ToString();

                            if (string.IsNullOrEmpty(reader["Param_DiagnosisInDischargeNote"].ToString()))
                            {
                                aPatientDTO.Param_DiagnosisInDischargeNote = null;
                            }
                            else
                            {
                                aPatientDTO.Param_DiagnosisInDischargeNote = Convert.ToInt32(reader["Param_DiagnosisInDischargeNote"]);
                            }

                            if (string.IsNullOrEmpty(reader["Param_MalignantDiagnosis"].ToString()))
                            {
                                aPatientDTO.Param_MalignantDiagnosis = null;
                            }
                            else
                            {
                                aPatientDTO.Param_MalignantDiagnosis = Convert.ToInt32(reader["Param_MalignantDiagnosis"]);
                            }

                            aPatientDTO.Param_TimeUntilBowelMovement = Convert.ToInt32(reader["Param_TimeUntilBowelMovement"]);
                            aPatientDTO.Param_ReSurgery = Convert.ToBoolean(reader["Param_ReSurgery"]);
                            aPatientDTO.Param_DaysAdmitted = Convert.ToDateTime(reader["Param_DaysAdmitted"]);
                            aPatientDTO.Param_HoursAdmitted = Convert.ToDateTime(reader["Param_HoursAdmitted"]);
                            aPatientDTO.Param_Dead = Convert.ToBoolean(reader["Param_Dead"]);
                            aPatientDTO.Param_DateOfDeath = Convert.ToDateTime(reader["Param_DateOfDeath"]);
                            aPatientDTO.Param_DeadWithin30DaysFromAdmission = Convert.ToBoolean(reader["Param_DeadWithin30DaysFromAdmission"]);
                            aPatientDTO.Param_ReAdmissionDueToSbo = Convert.ToBoolean(reader["Param_ReAdmissionDueToSbo"]);
                            aPatientDTO.Param_DateForReAdmission = Convert.ToDateTime(reader["Param_DateForReAdmission"]);

                            /*--- B Section Parameters (5 fields) ---*/
                            aPatientDTO.Param_CrpD0 = Convert.ToInt32(reader["Param_CrpD0"]);
                            aPatientDTO.Param_CrpMax = Convert.ToInt32(reader["Param_CrpMax"]);
                            aPatientDTO.Param_KreaD0 = Convert.ToInt32(reader["Param_KreaD0"]);
                            aPatientDTO.Param_KreaMax = Convert.ToInt32(reader["Param_KreaMax"]);
                            aPatientDTO.Param_AbdominalPain = Convert.ToBoolean(reader["Param_AbdominalPain"]);

                            /*--- C Section Parameters (11 fields) ---*/
                            aPatientDTO.Param_VomitBeforeAdmission = Convert.ToBoolean(reader["Param_VomitBeforeAdmission"]);
                            aPatientDTO.Param_VsondInitially = Convert.ToBoolean(reader["Param_VsondInitially"]);

                            if (string.IsNullOrEmpty(reader["Param_VsondLossesMax"].ToString()))
                            {
                                aPatientDTO.Param_VsondLossesMax = null;
                            }
                            else
                            {
                                aPatientDTO.Param_VsondLossesMax = Convert.ToInt32(reader["Param_VsondLossesMax"]);
                            }

                            aPatientDTO.Param_LengthOfHistory = Convert.ToInt32(reader["Param_LengthOfHistory"]);
                            aPatientDTO.Param_ClinicalEvaluation = Convert.ToInt32(reader["Param_ClinicalEvaluation"]);
                            aPatientDTO.Param_TypeOfSurgery = Convert.ToInt32(reader["Param_TypeOfSurgery"]);
                            aPatientDTO.Param_2ndSurgery = Convert.ToInt32(reader["Param_2ndSurgery"]);
                            aPatientDTO.Param_WasRadiologicAnswerCorrect = Convert.ToInt32(reader["Param_WasRadiologicAnswerCorrect"]);
                            aPatientDTO.Param_TimeUnderSurgery = Convert.ToInt32(reader["Param_TimeUnderSurgery"]);
                            aPatientDTO.Param_PostoperativeComplications = Convert.ToInt32(reader["Param_PostoperativeComplications"]);
                            aPatientDTO.Param_DaysAtIcu = Convert.ToInt32(reader["Param_DaysAtIcu"]);

                            /*--- D Section Parameters (7 fields) ---*/
                            aPatientDTO.Param_1stRadiology = Convert.ToInt32(reader["Param_1stRadiology"]);
                            aPatientDTO.Param_2ndRadiology = Convert.ToInt32(reader["Param_2ndRadiology"]);
                            aPatientDTO.Param_3rdRadiology = Convert.ToInt32(reader["Param_3rdRadiology"]);
                            aPatientDTO.Param_4thRadiology = Convert.ToInt32(reader["Param_4thRadiology"]);
                            aPatientDTO.Param_5thRadiology = Convert.ToInt32(reader["Param_5thRadiology"]);
                            aPatientDTO.Param_RadiologyAnswer = Convert.ToInt32(reader["Param_RadiologyAnswer"]);
                            aPatientDTO.Param_CirculatoryAffectedBowels = Convert.ToBoolean(reader["Param_CirculatoryAffectedBowels"]);

                            /*--- Extra field Parameters ---*/
                            aPatientDTO.Param_NumberOfReAdmissions = reader["Param_NumberOfReAdmissions"].ToString();
                            aPatientDTO.IsReviewed = false;
                            aPatientDTO.IsFlagged = false;

                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aPatientDTO;
        }

        public int GetTotalNumberOfPatients(int studyId)
        {
            int totalNumberOfPatients = 0;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT COUNT(*) AS TotalNumberOfPatients FROM StudyPatient WHERE StudyId = @StudyId", sqlConnection);
                    command.Parameters.AddWithValue("StudyId", studyId);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            totalNumberOfPatients = Convert.ToInt32(reader["TotalNumberOfPatients"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return totalNumberOfPatients;
        }

        public int GetTotalNumberOfPatientsFlagged(int studyId)
        {
            int numberOfPatientsFlagged = 0;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT COUNT(*) AS NumberOfPatientsFlagged FROM StudyPatient WHERE StudyId = @StudyId AND IsFlagged = 1", sqlConnection);
                    command.Parameters.AddWithValue("StudyId", studyId);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            numberOfPatientsFlagged = Convert.ToInt32(reader["NumberOfPatientsFlagged"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return numberOfPatientsFlagged;
        }

        public int GetTotalNumberOfPatientsReviewed(int studyId)
        {
            int numberOfPatientsReviewed = 0;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT COUNT(*) AS NumberOfPatientsReviewed FROM StudyPatient WHERE StudyId = @StudyId AND IsReviewed = 1", sqlConnection);
                    command.Parameters.AddWithValue("StudyId", studyId);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            numberOfPatientsReviewed = Convert.ToInt32(reader["NumberOfPatientsReviewed"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return numberOfPatientsReviewed;
        }

        public bool UpdateFlagProperty(int studyId, int patientId, bool flagValue)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE StudyPatient SET IsFlagged = @FlagValue WHERE StudyId = @StudyId AND PatientId = @PatientId", sqlConnection);
                command.Parameters.AddWithValue("FlagValue", flagValue);
                command.Parameters.AddWithValue("StudyId", studyId);
                command.Parameters.AddWithValue("PatientId", patientId);

                command.ExecuteNonQuery();
                return true;
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public bool UpdateReviewProperty(int studyId, int patientId, bool reviewValue)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE StudyPatient SET IsReviewed = @ReviewValue WHERE StudyId = @StudyId AND PatientId = @PatientId", sqlConnection);
                command.Parameters.AddWithValue("ReviewValue", reviewValue);
                command.Parameters.AddWithValue("StudyId", studyId);
                command.Parameters.AddWithValue("PatientId", patientId);

                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public int EtractPatients(int studyId)
        {
            try
            {
                sqlConnection.Open();
                SqlCommand command = new SqlCommand("ExtractPatients", sqlConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@StudyId", studyId));

                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }
    }
}
